
-- 1. 声明一个变量为smalldatetime，并设置为10分钟后的时间，然后打印。
DECLARE @smalldatetime DATETIME = DATEADD(MINUTE,10,GETDATE())
PRINT @smalldatetime
-- 2. 已知身份证号：420107199507104133，
--		请查询出对应的银行卡号、余额、状态。
DECLARE @A VARCHAR(30) = '420107199507104133'
SELECT BankCard.CardNo,BankCard.CardBalance,BankCard.CardState FROM BankCard INNER JOIN AccountInfo 
ON BankCard.AccountId = AccountInfo.AccountId 
WHERE AccountInfo.AccountCode=@A
--		请查询出存钱记录、取钱记录、转账记录。使用变量的方式。
--存钱记录
DECLARE @D VARCHAR(30) = '6225125478544587'
DECLARE @E VARCHAR(30) = '6225547858741263'
DECLARE @F VARCHAR(30) = '6225547854125656'
SELECT MoneyInBank FROM CardExchange WHERE CardNo = @D
SELECT MoneyInBank FROM CardExchange WHERE CardNo = @E
SELECT MoneyInBank FROM CardExchange WHERE CardNo = @F
--取钱记录
DECLARE @G VARCHAR(30) = '6225125478544587'
DECLARE @H VARCHAR(30) = '6225547858741263'
DECLARE @I VARCHAR(30) = '6225547854125656'
SELECT MoneyOutBank FROM CardExchange WHERE CardNo = @G
SELECT MoneyOutBank FROM CardExchange WHERE CardNo = @H
SELECT MoneyOutBank FROM CardExchange WHERE CardNo = @I
--转账记录
DECLARE @B VARCHAR(30) = '6225125478544587' 
DECLARE @C VARCHAR(30) = '6225547854125656'
DECLARE @J VARCHAR(30) = '6225547858741263' 
DECLARE @K VARCHAR(30) = '6225547854148645' 
SELECT * FROM CardTransfer WHERE CardNoOut = @B
SELECT * FROM CardTransfer WHERE CardNoOut = @C
SELECT * FROM CardTransfer WHERE CardNoOut = @J
SELECT * FROM CardTransfer WHERE CardNoOut = @K
-- 3. 张飞给刘备转账 10000 元，请写出相关sql
UPDATE BankCard SET CardBalance = CardBalance - 10000 WHERE AccountId = 3
UPDATE BankCard SET CardBalance = CardBalance + 10000 WHERE AccountId = 1
-- 4. 关羽取出 1000 元，请写出相关sql
SELECT * FROM CardExchange
UPDATE CardExchange SET MoneyOutBank = MoneyOutBank - 1000 WHERE CardNo = '6225547858741263'
-- 5. 刘备存入 20000 元， 请写出相关sql
UPDATE CardExchange SET MoneyInBank = MoneyInBank + 20000 WHERE CardNo = '6225125478544587'
-- 6. 张飞给赵云转账 1000元，请写出相关sql
UPDATE BankCard SET CardBalance = CardBalance -1000 WHERE AccountId = 3
UPDATE BankCard SET CardBalance = CardBalance + 1000 WHERE AccountId = 4
-- 7. 赵云的卡，申请挂失，请写出相关的sql
SELECT * FROM CardStateChange
INSERT INTO CardStateChange(CardNo,OldState,NewState,StateWhy,StateTime) VALUES ('6225547854148645',1,2,'老子卡多',GETDATE())
-- 8. 数据库密码为什么不能使用明文存储? 有哪些相关的加密方法?
--因为TMD会被人偷掉
--加密方法：MD5加密、DES加密、RSA加密


---景衡光》》》

--1. 声明一个变量为smalldatetime，并设置为10分钟后的时间，然后打印。
 declare @smalldatetime datetime= dateadd(MI,10,getdate())
 print @smalldatetime

 --2. 已知身份证号：420107199507104133，请查询出对应的银行卡号、余额、状态。 
 declare @AccountCode varchar(18)='420107199507104133' 
 select b.CardNo,b.CardBalance,b.CardState from AccountInfo a 
 left join BankCard b on a.AccountId=b.AccountId
 where AccountCode=@AccountCode


 --子查询
 declare @AccountCode varchar(18)='420107199507104133' 
select CardNo,CardBalance,CardState from BankCard where AccountId in (select AccountId from AccountInfo where AccountCode= @AccountCode)


--请查询出存钱记录、取钱记录、转账记录。使用变量的方式。
select * from BankCard 
select * from CardTransfer
 
declare @CardNo varchar(19) =(select CardNo from BankCard where AccountId in( select AccountId from AccountInfo where AccountCode='420107199507104133' ))
select * from CardTransfer where CardNoOut=@CardNo or CardNoIn=@CardNo

-- 3. 张飞给刘备转账 10000  元，请写出相关sql
--张飞卡号
declare @ZFcardNo varchar(19)='6225547854125656'
--刘备卡号
declare @LBcardNo varchar(19)='6225125478544587'
--转账金额
declare @money int=10000 

update BankCard set CardBalance=CardBalance+@money where CardNo=@LBcardNo
update BankCard set CardBalance=CardBalance-@money where CardNo=@ZFcardNo
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime)values(@ZFcardNo,@LBcardNo,@money,getdate())

select * from BankCard
select * from CardTransfer

-- 4. 关羽取出 1000 元，请写出相关sql
declare @money int= 1000
--关羽卡号
declare @GYcardNo varchar(19)='6225547858741263'

insert into CardExchange (CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)values(@GYcardNo,0,@money,GETDATE())
update BankCard set CardBalance=CardBalance-@money where CardNo=@GYcardNo

select * from CardExchange

-- 5. 刘备存入 20000 元， 请写出相关sql
insert into CardExchange (CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)values('6225125478544587',20000,0,GETDATE())
update BankCard set CardBalance=CardBalance+20000 where CardNo='6225125478544587'
-- 6. 张飞给赵云转账 1000元，请写出相关sql
update BankCard set CardBalance=CardBalance-1000where CardNo='6225547854125656'  --张飞
update BankCard set CardBalance=CardBalance+1000 where CardNo='6225125478544588' --赵云
insert into CardTransfer(CardNoOut,CardNoIn,TransferMoney,TransferTime) values('6225547854125656','6225125478544588',1000,getdate())

-- 7. 赵云的卡，申请挂失，请写出相关的sql
select * from CardStateChange 
insert into CardStateChange(CardNo,OldState,NewState,StateWhy,StateTime)
                     values ('6225547854148645',1,4,'因为我太有钱了，闲的',getdate())

-- 8. 数据库密码为什么不能使用明文存储? 有哪些相关的加密方法?
--因为TMD会被人偷掉
--加密方法：MD5加密、DES加密、RSA加密